
import pandas as pd

def 保存佣金信息(公司序号,产品序号,单元序号,佣金类型,佣金模板,资金账号):

    客户佣金信息=[]
    for index,row in 佣金模板.iterrows():
        if row["科目"]=="A股" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,1,"!","",1,2,row["费率"],5,0,1,"!"])   #上交所A-交易所业务-股票-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,1,"!","",1,2,row["费率"],5,0,1,"!"])   #深交所A-交易所业务-股票-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,1,"!","",4,2,row["费率"],5,0,1,"!"])  #上交所A-交易所业务-股票-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,1,"!","",4,2,row["费率"],5,0,1,"!"])  #深交所A-交易所业务-股票-佣金
        elif row["科目"]=="基金" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"F","!","",1,2,row["费率"],5,0,1,"!"])   #上交所A-交易所业务-开放式基金-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,"F","!","",1,2,row["费率"],5,0,1,"!"])  #深交所A-交易所业务-开放式基金-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,2,"!","",1,2,row["费率"],5,0,1,"!"])   #上交所A-交易所业务-封闭式基金-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,2,"!","",1,2,row["费率"],5,0,1,"!"])   #深交所A-交易所业务-封闭式基金-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"F","!","",4,2,row["费率"],5,0,1,"!"])   #上交所A-交易所业务-开放式基金-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,"F","!","",4,2,row["费率"],5,0,1,"!"])  #深交所A-交易所业务-开放式基金-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,2,"!","",4,2,row["费率"],5,0,1,"!"])   #上交所A-交易所业务-封闭式基金-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,2,"!","",4,2,row["费率"],5,0,1,"!"])   #深交所A-交易所业务-封闭式基金-佣金
        elif row["科目"]=="债券ETF" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"F",3,"",1,2,row["费率"],5,0,1,"!"])   #上交所A-交易所业务-开放式基金-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,"F",3,"",1,2,row["费率"],5,0,1,"!"])  #深交所A-交易所业务-开放式基金-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"F",3,"",4,2,row["费率"],5,0,1,"!"])   #上交所A-交易所业务-开放式基金-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,"F",3,"",4,2,row["费率"],5,0,1,"!"])  #深交所A-交易所业务-开放式基金-佣金
        elif row["科目"]=="债券" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,3,"!","",1,2,row["费率"],1,0,1,"!"])   #上交所A-交易所业务-国债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,4,"!","",1,2,row["费率"],1,0,1,"!"])   #上交所A-交易所业务-企债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,6,"!","",1,2,row["费率"],1,0,1,"!"])   #上交所A-交易所业务-政策性金融债债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"S","!","",1,2,row["费率"],1,0,1,"!"])  #上交所A-交易所业务-公司债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"T","!","",1,2,row["费率"],1,0,1,"!"])  #上交所A-交易所业务-地方债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,3,"!","",1,2,row["费率"],0,0,1,"!"])   #深交所A-交易所业务-国债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,4,"!","",1,2,row["费率"],0,0,1,"!"])  #深交所A-交易所业务-企债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,6,"!","",1,2,row["费率"],1,0,1,"!"])   #深交所A-交易所业务-政策性金融债债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,"S","!","",1,2,row["费率"],0,0,1,"!"])  #深交所A-交易所业务-公司债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,"T","!","",1,2,row["费率"],0,0,1,"!"])  #深交所A-交易所业务-地方债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I",3,"!","",1,2,row["费率"],1,0,1,"!"])  #上交所A-固收平台-国债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I",4,"!","",1,2,row["费率"],1,0,1,"!"])  #上交所A-固收平台-企债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I",6,"!","",1,2,row["费率"],1,0,1,"!"])  #上交所A-固收平台-政策性金融债债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I","S","!","",1,2,row["费率"],1,0,1,"!"])  #上交所A-固收平台-公司债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I","T","!","",1,2,row["费率"],1,0,1,"!"])  #上交所A-固收平台-地方债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",3,"!","",1,2,row["费率"],0,0,1,"!"])   #深交所A-大宗交易-国债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",4,"!","",1,2,row["费率"],0,0,1,"!"])    #深交所A-大宗交易-企债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",6,"!","",1,2,row["费率"],0,0,1,"!"])  #深交所A-大宗交易-政策性金融债债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E","S","!","",1,2,row["费率"],0,0,1,"!"])  #深交所A-大宗交易-公司债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E","T","!","",1,2,row["费率"],0,0,1,"!"])  #深交所A-大宗交易-地方债-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,3,"!","",4,2,row["费率"],1,0,1,"!"])   #上交所A-交易所业务-国债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,4,"!","",4,2,row["费率"],1,0,1,"!"])   #上交所A-交易所业务-企债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,6,"!","",4,2,row["费率"],1,0,1,"!"])   #上交所A-交易所业务-政策性金融债债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"S","!","",4,2,row["费率"],1,0,1,"!"])  #上交所A-交易所业务-公司债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"T","!","",4,2,row["费率"],1,0,1,"!"])  #上交所A-交易所业务-地方债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,3,"!","",4,2,row["费率"],0,0,1,"!"])   #深交所A-交易所业务-国债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,4,"!","",4,2,row["费率"],0,0,1,"!"])  #深交所A-交易所业务-企债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,6,"!","",4,2,row["费率"],1,0,1,"!"])   #深交所A-交易所业务-政策性金融债债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,"S","!","",4,2,row["费率"],0,0,1,"!"])  #深交所A-交易所业务-公司债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,"T","!","",4,2,row["费率"],0,0,1,"!"])  #深交所A-交易所业务-地方债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I",3,"!","",4,2,row["费率"],1,0,1,"!"])  #上交所A-固收平台-国债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I",4,"!","",4,2,row["费率"],1,0,1,"!"])  #上交所A-固收平台-企债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I",6,"!","",4,2,row["费率"],1,0,1,"!"])  #上交所A-固收平台-政策性金融债债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I","S","!","",4,2,row["费率"],1,0,1,"!"])  #上交所A-固收平台-公司债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I","T","!","",4,2,row["费率"],1,0,1,"!"])  #上交所A-固收平台-地方债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",3,"!","",4,2,row["费率"],0,0,1,"!"])   #深交所A-大宗交易-国债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",4,"!","",4,2,row["费率"],0,0,1,"!"])    #深交所A-大宗交易-企债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",6,"!","",4,2,row["费率"],0,0,1,"!"])  #深交所A-大宗交易-政策性金融债债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E","S","!","",4,2,row["费率"],0,0,1,"!"])  #深交所A-大宗交易-公司债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E","T","!","",4,2,row["费率"],0,0,1,"!"])  #深交所A-大宗交易-地方债-交易费
        elif row["科目"]=="债券转股回售" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"B","!","",1,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","B","!","",1,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"y","!","",1,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","y","!","",1,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"B","!","",4,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","B","!","",4,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"y","!","",4,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","y","!","",4,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
        elif row["科目"]=="上海债券转股" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"B","!","",1,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","B","!","",1,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"B","!","",4,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","B","!","",4,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
        elif row["科目"]=="上海可转债回售" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"y","!","",1,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","y","!","",1,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"y","!","",4,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","y","!","",4,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
        elif row["科目"]=="上海债券回售" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"y","!","",1,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","y","!","",1,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,"y","!","",4,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E","y","!","",4,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费






        elif row["科目"]=="深市可转债" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,5,"g","",1,2,row["费率"],0,0,1,"!"]) #深交所A-交易所业务-可转债-分离债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",5,"g","",1,2,row["费率"],0,0,1,"!"])  #深交所A-大宗交易平台-可转债-分离债-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,5,"g","",4,2,row["费率"],0,0,1,"!"]) #深交所A-交易所业务-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",5,"g","",4,2,row["费率"],0,0,1,"!"])  #深交所A-大宗交易平台-可转债-佣金

        elif row["科目"]=="沪市可转债" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,5,"g","",1,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,"g","",1,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,5,"g","",4,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,"g","",4,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-佣金
        elif row["科目"]=="深市可交换债" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,5,5,"",1,2,row["费率"],0,0,1,"!"]) #深交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,5,6,"",1,2,row["费率"],0,0,1,"!"]) #深交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,5,"n","",1,2,row["费率"],0,0,1,"!"]) #深交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",5,5,"",1,2,row["费率"],0,0,1,"!"])  #深交所A-大宗交易平台-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",5,6,"",1,2,row["费率"],0,0,1,"!"])  #深交所A-大宗交易平台-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",5,"n","",1,2,row["费率"],0,0,1,"!"])  #深交所A-大宗交易平台-可转债-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,5,5,"",4,2,row["费率"],0,0,1,"!"]) #深交所A-交易所业务-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,5,6,"",4,2,row["费率"],0,0,1,"!"]) #深交所A-交易所业务-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,5,"n","",4,2,row["费率"],0,0,1,"!"]) #深交所A-交易所业务-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",5,5,"",4,2,row["费率"],0,0,1,"!"])  #深交所A-大宗交易平台-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",5,6,"",4,2,row["费率"],0,0,1,"!"])  #深交所A-大宗交易平台-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E",5,"n","",4,2,row["费率"],0,0,1,"!"])  #深交所A-大宗交易平台-可转债-佣金

        elif row["科目"]=="沪市可交换债" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,5,5,"",1,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,5,6,"",1,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,5,"n","",1,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,5,"",1,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,6,"",1,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,"j","",1,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,"n","",1,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-交易费

            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,5,5,"",4,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,5,6,"",4,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,5,"n","",4,2,row["费率"],1,0,1,"!"])#上交所A-交易所业务-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,5,"",4,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,6,"",4,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,"j","",4,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"E",5,"n","",4,2,row["费率"],1,0,1,"!"])#上交所A-上交所固定收益平台-可转债-佣金

        elif row["科目"]=="新三板" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,"j",1,1,"!","",1,2,row["费率"],0,0,1,"!"])  #股转市场-交易所业务-股票-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,"j",1,1,"!","",4,2,row["费率"],0,0,1,"!"])  #股转市场-交易所业务-股票-佣金

        elif row["科目"]=="北交所A股" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,"r",1,1,"!","",1,2,row["费率"],0,0,1,"!"])  #北交所-交易所业务-股票-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,"r",1,1,"!","",4,2,row["费率"],0,0,1,"!"])   #北交所-交易所业务-股票-佣金


        elif row["科目"]=="港股通" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,"n",1,1,"!","",1,2,row["费率"],2,0,1,"!"]) #港股通沪-交易所业务-股票-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,"o",1,1,"!","",1,2,row["费率"],2,0,1,"!"])  #港股通深-交易所业务-股票-交易费
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,"n",1,1,"!","",4,2,row["费率"],2,0,1,"!"]) #港股通沪-交易所业务-股票-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,"o",1,1,"!","",4,2,row["费率"],2,0,1,"!"])  #港股通深-交易所业务-股票-佣金
        elif row["科目"]=="债券回购" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204001,1,2,0.01*row["费率"],0,0,1,"!"])  #上交所A-交易所业务-债券回购-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204002,1,2,0.02*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204003,1,2,0.03*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204004,1,2,0.04*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204007,1,2,0.05*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204014,1,2,0.1*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204028,1,2,0.2*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204091,1,2,0.3*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204182,1,2,0.3*row["费率"],0,0,1,"!"])

                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131810,1,2,0.01*row["费率"],0,0,1,"!"]) #深交所A-交易所业务-债券回购-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131811,1,2,0.02*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131800,1,2,0.03*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131809,1,2,0.04*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131801,1,2,0.05*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131802,1,2,0.1*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131803,1,2,0.2*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131805,1,2,0.3*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131806,1,2,0.3*row["费率"],0,0,1,"!"])
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204001,4,2,0.01*row["费率"],0,0,1,"!"])  #上交所A-交易所业务-债券回购-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204002,4,2,0.02*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204003,4,2,0.03*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204004,4,2,0.04*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204007,4,2,0.05*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204014,4,2,0.1*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204028,4,2,0.2*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204091,4,2,0.3*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,1,1,8,"!",204182,4,2,0.3*row["费率"],0,0,1,"!"])

                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131810,4,2,0.01*row["费率"],0,0,1,"!"])  #深交所A-交易所业务-债券回购-佣金
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131811,4,2,0.02*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131800,4,2,0.03*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131809,4,2,0.04*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131801,4,2,0.05*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131802,4,2,0.1*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131803,4,2,0.2*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131805,4,2,0.3*row["费率"],0,0,1,"!"])
                客户佣金信息.append([公司序号,产品序号,单元序号,2,1,8,"!",131806,4,2,0.3*row["费率"],0,0,1,"!"])


        elif row["科目"]=="质押式协议回购" and row["费率"]!=0:
            if 佣金类型=="全佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I","-","!","",1,2,row["费率"],0,0,1,"!"])  #上交所A-上交所固定收益平台-协议质押式回购-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E","-","!","",1,2,row["费率"],0,0,1,"!"])  #上交所A-交易所业务-协议质押式回购-佣金
            elif 佣金类型=="净佣":
                客户佣金信息.append([公司序号,产品序号,单元序号,1,"I","-","!","",4,2,row["费率"],0,0,1,"!"])  #深交所A-交易所业务-协议质押式回购-交易费
                客户佣金信息.append([公司序号,产品序号,单元序号,2,"E","-","!","",4,2,row["费率"],0,0,1,"!"])  #深交所A-交易所业务-协议质押式回购-佣金


    生成数据=pd.DataFrame(客户佣金信息,columns=["公司序号","产品序号","单元序号","交易市场","业务分类","证券类别","费用子类","证券代码","费用类别","计费方式","费率","最低费用","最高费用","合笔方式","委托方向"])
    df_no_duplicates = 生成数据.drop_duplicates(keep="last")
    df_no_duplicates.to_excel(f"./{资金账号}_生成结果.xlsx",index=False)
    print(f"西藏信托{资金账号}佣金模板已生成")




#读取账户信息
账户信息=pd.read_excel("./account.xlsx")
西藏佣金=pd.read_excel("./xz.xlsx")
for index,row in 西藏佣金.iterrows():
    资金账号=row["资金账号"]
    A股=row["A股"]
    基金=row["基金"]
    债券=row["债券"]
    债券转股回售=row["债券转股回售"]
    沪市可转债=row["沪市可转债"]
    沪市可交换债=row["沪市可交换债"]
    深市可转债=row["深市可转债"]
    深市可交换债=row["深市可交换债"]
    新三板=row["新三板"]
    北交所A股=row["北交所A股"]
    质押式协议回购=row["质押式协议回购"]
    其他_上海债券回售=row["上海债券回售"]
    其他_上海债券转股=row["上海债券转股"]
    其他_上海可转债回售=0
    港股通=row["港股通"]
    债券回购=row["债券回购"]

    佣金列表=[["A股",A股],["基金",基金],["债券",债券],["债券转股回售",债券转股回售],["沪市可转债",沪市可转债],["沪市可交换债",沪市可交换债],["深市可转债",深市可转债],["深市可交换债",深市可交换债],
      ["新三板",新三板],["北交所A股",北交所A股],["质押式协议回购",质押式协议回购],["其他_上海债券回售",其他_上海债券回售],["其他_上海债券转股",其他_上海债券转股],["其他_上海可转债回售",其他_上海可转债回售]
      ,["港股通",港股通],["债券回购",债券回购]]

    表头=["科目","费率"]
    佣金模板=pd.DataFrame(佣金列表,columns=表头)
    公司序号=账户信息[账户信息["资金账号"]==资金账号]["公司序号"].iloc[0]
    产品序号=账户信息[账户信息["资金账号"]==资金账号]["产品序号"].iloc[0]
    单元序号=账户信息[账户信息["资金账号"]==资金账号]["单元序号"].iloc[0]
    佣金类型=账户信息[账户信息["资金账号"]==资金账号]["佣金类型"].iloc[0]

    保存佣金信息(公司序号,产品序号,单元序号,"全佣",佣金模板,资金账号)




# 佣金生成数据=pd.DataFrame(columns=["公司序号","产品序号","单元序号","交易市场","业务分类","证券类别","费用子类","证券代码","费用类别","计费方式","费率","最低费用","最高费用","合笔方式","委托方向"])



input("Press Enter to exit…")

# oneData=fullData.drop_duplicates(subset=['发生日期',"操作员编号"])
# fullData.to_csv(r"C:\Users\AXZQ\Desktop\新建文件夹\恒生PB\all.csv")
# oneData.to_csv(r"C:\Users\AXZQ\Desktop\新建文件夹\恒生PB\one.csv")
